Skip to main content

Transform a column-store table into a row-store table

Overview​

This document provides information about Transform a column-store table into a row-store table.

This scenario is based on the gateways table transformation, replace the structure with the one from the table you want to transform.

  1. Create the row-store table

Create a copy of the table you want to transform, using a different name.

CREATE ROWSTORE TABLE `gateways_rowstore` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint(20) unsigned NOT NULL,
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `order` int(11) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `own_gateway` tinyint(1) NOT NULL,
  `retail_enabled` tinyint(1) NOT NULL,
  `subscription_enabled` tinyint(1) NOT NULL,
  `supported_currencies` JSON COLLATE utf8_general_ci NOT NULL,
  `settings` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `temp_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `gateways_tenant_id_index` (`tenant_id`),
  KEY `gateways_tenant_id_type_index` (`tenant_id`, `type`),
  SHARD KEY `__SHARDKEY` (`id`)
);
  1. Copy data from the original column-store to the new row-store
INSERT INTO gateways_rowstore SELECT * FROM gateways;
  1. Rename the tables to "apply" the change
alter table gateways rename to gateways_old;
alter table gateways_rowstore rename to gateways;
X

Graph View